﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using DAL;
using System.Data.SqlClient;
using VOL;

namespace BLL
{
    public class DMCanBoBL
    {
         private DataAccess dataAccess;

         public DMCanBoBL()
        {
            dataAccess = new DataAccess();
        }

        /// <summary>
         /// 1. Hiển thị danh sách cán bộ
        /// </summary>
        /// <returns></returns>
        public DataTable GetData()
        {
            // cau query select
            string sql = "SELECT tblDMCANBO.IDCANBO, tblDMCANBO.HOTEN, tblDMCANBO.PHONGBAN, tblDMCHUCVU.TENCHUCVU FROM tblDMCANBO INNER JOIN tblDMCHUCVU ON tblDMCANBO.IDCHUCVU = tblDMCHUCVU.IDCHUCVU ORDER BY tblDMCANBO.IDCANBO DESC ";

            DataTable dt = new DataTable();
            // do toan bo du lieu vao datatable
            dt = dataAccess.GetData(sql);
            return dt;
        }

        /// <summary>
        /// 1. Hiển thị danh sách cán bộ trong fomr thu chi
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataCB()
        {
            // cau query select
            string sql = "SELECT IDCANBO, HOTEN FROM tblDMCANBO ";

            DataTable dt = new DataTable();
            // do toan bo du lieu vao datatable
            dt = dataAccess.GetData(sql);
            return dt;
        }

        /// <summary>
        /// 2. Thêm cán bộ
        /// </summary>
        /// <param name="obj">DMCanBo</param>
        /// <returns>true</returns>
        public bool InsertData(DMCanBo obj)
        {
            SqlParameter[] param = new SqlParameter[7];
            param[0] = new SqlParameter("@HoTen", SqlDbType.NVarChar) { Value = obj.HoTen };
            param[1] = new SqlParameter("@NgaySinh", SqlDbType.DateTime) { Value = obj.NgaySinh };
            param[2] = new SqlParameter("@PhongBan", SqlDbType.NVarChar) { Value = obj.PhongBan };
            param[3] = new SqlParameter("@IDChucVu", SqlDbType.Int) { Value = obj.ChucVu };
            param[4] = new SqlParameter("@TenTruyCap", SqlDbType.NVarChar) { Value = obj.TenTruyCap };
            param[5] = new SqlParameter("@MatKhau", SqlDbType.NVarChar) { Value = obj.MatKhau };
            param[6] = new SqlParameter("@Admin", SqlDbType.Bit) { Value = obj.Admin };
            // cau query select
            string sql = "INSERT INTO tblDMCANBO (HOTEN, NGAYSINH, PHONGBAN, IDCHUCVU, TENTRUYCAP, MATKHAU, admin) VALUES (@HoTen, @NgaySinh, @PhongBan, @IDChucVu, @TenTruyCap, @MatKhau, @Admin)";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 3. Xóa cán bộ
        /// </summary>
        /// <param name="MaNguoiDung">IDCANBO INT</param>
        /// <returns>true</returns>
        public bool DeleteData(int IDCANBO)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = IDCANBO };
            // cau query select
            string sql = "DELETE FROM tblDMCANBO WHERE IDCANBO = @IDCANBO";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 4. sửa cán bộ
        /// </summary>
        /// <param name="obj">CANBO</param>
        /// <returns>true</returns>
        public bool UpdateData(DMCanBo obj)
        {
            SqlParameter[] param = new SqlParameter[8];
            param[0] = new SqlParameter("@HoTen", SqlDbType.NVarChar) { Value = obj.HoTen };
            param[1] = new SqlParameter("@NgaySinh", SqlDbType.DateTime) { Value = obj.NgaySinh };
            param[2] = new SqlParameter("@PhongBan", SqlDbType.NVarChar) { Value = obj.PhongBan };
            param[3] = new SqlParameter("@IDChucVu", SqlDbType.Int) { Value = obj.ChucVu };
            param[4] = new SqlParameter("@TenTruyCap", SqlDbType.NVarChar) { Value = obj.TenTruyCap };
            param[5] = new SqlParameter("@MatKhau", SqlDbType.NVarChar) { Value = obj.MatKhau };
            param[6] = new SqlParameter("@Admin", SqlDbType.Bit) { Value = obj.Admin };

            param[7] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = obj.IDCanBo };
            // cau query select
            string sql = "UPDATE tblDMCANBO SET HOTEN = @HoTen, NGAYSINH = @NgaySinh, PHONGBAN = @PhongBan, IDCHUCVU = @IDChucVu, TENTRUYCAP = @TenTruyCap, MATKHAU = @MatKhau, admin = @Admin " +
                           " WHERE IDCANBO = @IDCANBO";
            dataAccess.Execute(sql, param);

            return true;
        }

        //4.1. cap nhat truong da nop het trong truong hop thu du 12 thang
        public bool UpdateDataCBNopHet(int IDCANBO, int NOPHET)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@NOPHET", SqlDbType.NVarChar) { Value = NOPHET };
            param[1] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = IDCANBO };
            // cau query select
            string sql = "UPDATE tblDMCANBO SET NOPHET = @NOPHET " +
                           " WHERE IDCANBO = @IDCANBO";
            dataAccess.Execute(sql, param);

            return true;
        }

        /// <summary>
        /// 5. Tìm theo Mã CÁN BỘ
        /// </summary>
        /// <param name="IDCANBO"></param>
        /// <returns></returns>
        public DataTable GetDataById(int IDCANBO)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = IDCANBO };
            // cau query select
            string sql = "SELECT * FROM tblDMCANBO WHERE IDCANBO = @IDCANBO";
            DataTable dt = dataAccess.GetData(sql, param);

            return dt;
        }

        /// <summary>
        /// 5.1. Tìm theo Mã CÁN BỘ
        /// </summary>
        /// <param name="DMCANBO"></param>
        /// <returns></returns>
        //Tìm
        public DataTable searchIDCANBO(DMCanBo obj)
        {
            SqlParameter[] param = new SqlParameter[1];
            param[0] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = obj.IDCanBo };

            string strSql = "SELECT * FROM tblDMCANBO WHERE IDCANBO = @IDCANBO";
            return dataAccess.GetData(strSql, param);
        }

        // 6. Login
        public DMCanBo Login(string tenTruyCap, string matKhau)
        {
            DMCanBo obj = new DMCanBo();

            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@TENTRUYCAP", SqlDbType.NVarChar) { Value = tenTruyCap };
            param[1] = new SqlParameter("@MATKHAU", SqlDbType.NVarChar) { Value = matKhau };
            // cau query select
            string sql = "SELECT * FROM tblDMCANBO WHERE TENTRUYCAP = @TenTruyCap AND MATKHAU = @MatKhau";
            DataTable dt = dataAccess.GetData(sql, param);
            if (dt.Rows.Count > 0)
            {
                obj.TenTruyCap = tenTruyCap;
                obj.MatKhau = matKhau;
                obj.IDCanBo = int.Parse(dt.Rows[0]["IDCANBO"].ToString());
                obj.HoTen = dt.Rows[0]["HOTEN"].ToString();
                obj.Admin = bool.Parse(dt.Rows[0]["admin"].ToString());
                return obj;
            }

            return null;
        }
        // 7. Logout

        public bool ChangPass(DMCanBo obj)
        {
            SqlParameter[] param = new SqlParameter[2];
            param[0] = new SqlParameter("@MATKHAU", SqlDbType.NVarChar) { Value = obj.MatKhau };
            param[1] = new SqlParameter("@IDCANBO", SqlDbType.Int) { Value = obj.IDCanBo };
            // cau query select
            string sql = "UPDATE tblDMCANBO SET MATKHAU = @MatKhau WHERE IDCANBO = @IDCanBo";
            dataAccess.Execute(sql, param);

            return true;
        }
    }
}
